﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using ZX.DAL.DBExtend;
using ZX.CRM.Library;
using ZX.BLL;
using ZX.BLL.Extend;
namespace ZX.WebApp.Crm
{
    public partial class StudentHrSign : System.Web.UI.Page
    {
        public string termName
        {
            get;
            set;
        }
        public string gvHead
        {
            get;
            set;
        }
        /// <summary>
        /// 总人数
        /// </summary>
        public int totalNum
        {
            get;
            set;
        }
        /// <summary>
        /// 已分班人数
        /// </summary>
        public int partNum
        {
            get;
            set;
        }
        /// <summary>
        /// 未分班人数
        /// </summary>
        public int noPartNum
        {
            get;
            set;
        }
        /// <summary>
        /// 企业家数
        /// </summary>
        public int customerNum
        {
            get;
            set;
        }

        BaseBussnies bll = new BaseBussnies();
        protected void Page_Load(object sender, EventArgs e)
        {
            termName = Request["key"].ToString();
            if (!IsPostBack)
            {
                // 正确的属性设置方法
                this.gvStudent.Attributes.Add("SortExpression", "CustomerName");
                this.gvStudent.Attributes.Add("SortDirection", "ASC");
                bindDrop();
                if (Request["key"] != null)
                {
                    Inits("");
                }
            }
        }
        /// <summary>
        /// GridView排序事件
        /// </summary>
        protected void gvStudent_Sorting(object sender, GridViewSortEventArgs e)
        {
            // 从事件参数获取排序数据列
            string sortExpression = e.SortExpression.ToString();

            // 假定为排序方向为“顺序”
            string sortDirection = "ASC";

            // “ASC”与事件参数获取到的排序方向进行比较，进行GridView排序方向参数的修改
            if (sortExpression == this.gvStudent.Attributes["SortExpression"])
            {
                //获得下一次的排序状态
                sortDirection = (this.gvStudent.Attributes["SortDirection"].ToString() == sortDirection ? "DESC" : "ASC");
            }
            // 重新设定GridView排序数据列及排序方向
            this.gvStudent.Attributes["SortExpression"] = sortExpression;
            this.gvStudent.Attributes["SortDirection"] = sortDirection;
            Inits(getCondition());
        }


        /// <summary>
        /// 初始化数据
        /// </summary>
        /// <param name="condition">条件</param>
        private void Inits(string condition)
        {
            // 获取GridView排序数据列及排序方向
            string sortExpression = this.gvStudent.Attributes["SortExpression"];
            string sortDirection = this.gvStudent.Attributes["SortDirection"];
            DbSql db = new DbSql();
            DataTable dt = db.ExecuteQuery("select * from V_CrmMajorStudentHr where termName='" + termName + "' AND DliverState<>'73DAC2CE-F4BF-403C-9ACC-D075551E3328' and StudentState<>'转期'" + condition);
            if (dt == null || dt.Rows.Count == 0) { 
                gvHead = "1";
                totalNum = 0;
                partNum = 0;
                noPartNum = 0;
                customerNum = 0;
            } else { 
                gvStudent.ShowHeader = true;
                totalNum = dt.Rows.Count;
                DataTable newdt = new DataTable();
                newdt = dt.Clone(); // 克隆dt 的结构，包括所有 dt 架构和约束,并无数据；
                DataRow[] rows = dt.Select("ClassNo<>''"); // 从dt 中查询符合条件的记录；
                foreach (DataRow row in rows)  // 将查询的结果添加到dt中；
                {
                    newdt.Rows.Add(row.ItemArray);
                }
                partNum = newdt == null ? 0 : newdt.Rows.Count;
                noPartNum = totalNum - partNum;
                DataTable newCustomerDt = this.SelectDistinct(dt, "CustomerName");//返回过滤后的DataTable

                customerNum = newCustomerDt == null ? 0 : newCustomerDt.Rows.Count;
            }
            // 根据GridView排序数据列及排序方向设置显示的默认数据视图
            if ((!string.IsNullOrEmpty(sortExpression)) && (!string.IsNullOrEmpty(sortDirection)))
            {
                dt.DefaultView.Sort = string.Format("{0} {1}", sortExpression, sortDirection);
            }

            
            
            gvStudent.DataSource = dt;
            gvStudent.DataBind();
        }
        /// <summary>
        /// 获取查询条件
        /// </summary>
        /// <returns></returns>
        private string getCondition()
        {
            string sqlStr = "";
            if (CustomerName.Value.Trim()!="")
            {
                sqlStr = " and CustomerName like '%" + FmLikeSQL(CustomerName.Value) + "%'";
            }
            if (StudentName.Value.Trim() != "")
            {
                sqlStr = sqlStr + " and StudentName like '%" + FmLikeSQL(StudentName.Value) + "%'";
            }
            if (ClassNo.Value.Trim() != "")
            {
                sqlStr = sqlStr + " and ClassNo like '%" + FmLikeSQL(ClassNo.Value) + "%'";
            }
            if (ddlDeptName.SelectedValue.Trim() != "")
            {
                sqlStr = sqlStr + " and OrderDeptName like '%" + FmLikeSQL(ddlDeptName.SelectedValue) + "%'";
            }
            if (CustomerMgrName.Value.Trim() != "")
            {
                sqlStr = sqlStr + " and AddUserName like '%" +FmLikeSQL(CustomerMgrName.Value) + "%'";
            }
            if (ddlSex.SelectedItem.Text.Trim() != "")
            {
                sqlStr = sqlStr + " and sex = '" +FmSQL(ddlSex.SelectedItem.Text) + "'";
            }
            if (ddlIsPartClass.SelectedValue=="1"){
                sqlStr = sqlStr + " and ClassNo <>''";
            }
            if (ddlIsPartClass.SelectedValue == "0")
            {
                sqlStr = sqlStr + " and (ClassNo ='' or ClassNo is null)";
            }
            return sqlStr;
        }

        #region 过滤重复企业
        
        /// <summary>
        /// 返回执行Select distinct后的DataTable
        /// </summary>
        /// <param name="SourceTable">源数据表</param>
        /// <param name="FieldNames">字段集</param>
        /// <returns></returns>
        private DataTable SelectDistinct(DataTable SourceTable, params string[] FieldNames)
        {
            object[] lastValues;
            DataTable newTable;
            DataRow[] orderedRows;

            if (FieldNames == null || FieldNames.Length == 0)
                throw new ArgumentNullException("FieldNames");

            lastValues = new object[FieldNames.Length];
            newTable = new DataTable();

            foreach (string fieldName in FieldNames)
                newTable.Columns.Add(fieldName, SourceTable.Columns[fieldName].DataType);

            orderedRows = SourceTable.Select("", string.Join(",", FieldNames));

            foreach (DataRow row in orderedRows)
            {
                if (!fieldValuesAreEqual(lastValues, row, FieldNames))
                {
                    newTable.Rows.Add(createRowClone(row, newTable.NewRow(), FieldNames));

                    setLastValues(lastValues, row, FieldNames);
                }
            }

            return newTable;
        }

        private bool fieldValuesAreEqual(object[] lastValues, DataRow currentRow, string[] fieldNames)
        {
            bool areEqual = true;

            for (int i = 0; i < fieldNames.Length; i++)
            {
                if (lastValues[i] == null || !lastValues[i].Equals(currentRow[fieldNames[i]]))
                {
                    areEqual = false;
                    break;
                }
            }

            return areEqual;
        }

        private DataRow createRowClone(DataRow sourceRow, DataRow newRow, string[] fieldNames)
        {
            foreach (string field in fieldNames)
                newRow[field] = sourceRow[field];

            return newRow;
        }

        private void setLastValues(object[] lastValues, DataRow sourceRow, string[] fieldNames)
        {
            for (int i = 0; i < fieldNames.Length; i++)
                lastValues[i] = sourceRow[fieldNames[i]];
        } 
        #endregion

        #region SQL语句注入过滤
        /// <summary>
        /// 模糊查询关键字过滤
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        private string FmLikeSQL(object o)
        {
            if (o == null) return "";
            string s = "";
            if (o.GetType() == typeof(DateTime))
            {
                s = (Convert.ToDateTime(o)).ToString("yyyy-MM-dd HH:mm:ss");
            }
            else
            {
                s = o.ToString();
            }
            return s.Replace("[", "[[]").Replace("%", "[%]").Replace("_", "[_]").Replace("'", "''");
        }
        /// <summary>
        /// 过滤
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        private string FmSQL(object o)
        {
            if (o == null) return "";
            string s = "";
            if (o.GetType() == typeof(DateTime))
            {
                s = (Convert.ToDateTime(o)).ToString("yyyy-MM-dd HH:mm:ss");
            }
            else
            {
                s = o.ToString();
            }
            return s.Replace("'", "''");
        }
        #endregion
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnCheck_Click(object sender, EventArgs e) {
            Inits(getCondition());
        }
        /// <summary>
        /// 分班
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnPart_Click(object sender, EventArgs e) {
            for (int i = 0; i < gvStudent.Rows.Count; i++)
            {
                CheckBox cb = (CheckBox)gvStudent.Rows[i].FindControl("chkSel");
                if (cb.Checked)
                {
                    HiddenField hdn = gvStudent.Rows[i].FindControl("hdnId") as HiddenField;
                    string strSql = string.Format("update CrmMajorStudentHr set ClassId='{0}' where ID='{1}'", ddlClass.SelectedValue, hdn.Value);
                    bll.RunSqlCommand(strSql);
                }
            }
            Inits(getCondition());
        }
        private void bindDrop() {
            BDatPhraseDetail bllDetail = new BDatPhraseDetail();
            IList<CrmDeliverHrClass> ListDliverId = bllDetail.GetCrmDeliverHrClass();
            ddlClass.DataSource = ListDliverId;
            ddlClass.DataTextField = "ClassNo";
            ddlClass.DataValueField = "ID";
            ddlClass.DataBind();
            IList<V_CrmMajorStudentHr> ListDept = bllDetail.GetOrderDeptName(termName
);
            ddlDeptName.DataSource = ListDept;
            ddlDeptName.DataTextField = "OrderDeptName";
            ddlDeptName.DataValueField = "OrderDeptName";
            ddlDeptName.DataBind();
        }
    }
}